package com.infore.dataAudit.repository;

import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.infore.dataAudit.entity.TDataAudit;
import com.infore.dataAudit.entity.TDataAuditRecord;
import com.infore.dataAudit.vo.DataAuditRecordVO;

/**
 * 数据审核实体表操作接口
 * @author cys
 *
 */
public interface ITDataAuditRepository extends JpaRepository<TDataAudit, String>,
                JpaSpecificationExecutor<TDataAudit>{
    
    @EntityGraph("dataAudit_view")
    public List<TDataAudit> findByStationMnLike(String mn);
    
    @EntityGraph("dataAudit_view")
    public List<TDataAudit> findByFlowInstanceId(String flowId);
    
    /**
     * 查询某时间段内的某监测站的审核数据
     * 
     * @author 蔡永顺
     * 
     * @return List<TDataAudit>
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param mn 监测站mn
     * @return
     * @date 2019年4月18日上午9:07:51
     */
    @EntityGraph("dataAudit_view")
    public List<TDataAudit> findByCreateTimeBetweenAndStationMnOrderByCreateTimeAsc
        (Date startTime, Date endTime, String mn);
    
    
    /**查询站点审核数据 根据时间段与站点mn集合 ***/
    @EntityGraph("dataAudit_view")
    public List<TDataAudit> findByCreateTimeBetweenAndStationMnInOrderByCreateTimeAsc
        (Date startTime, Date endTime, List<String> mnList);
    
    /**分页查询站点审核数据 根据时间段与站点mn集合 ***/
    @EntityGraph("dataAudit_view")
    Page<TDataAudit> findByCreateTimeBetweenAndStationMnInOrderByCreateTimeAsc
    (Date startTime, Date endTime, List<String> mnList,Pageable pageable);
    
    
    /** 查询站点数据审核 审核数据        **/
    @Query(value = "SELECT s.STATION_NAME,r.* FROM T_DATA_AUDIT_RECORD r  LEFT JOIN\r\n" + 
            "(\r\n" + 
            "SELECT DISTINCT t.STATION_MN,t.STATION_NAME,TO_CHAR(t.C_TIME,'yyyy-mm-dd')ctime ,"
            + "t.FLOW_INSTANCE_ID FROM T_DATA_AUDIT t where t.C_TIME BETWEEN TO_DATE(:st, "
            + "'yyyy-mm-dd hh24:mi:ss')\r\n" + 
            "    and TO_DATE(:et, 'yyyy-mm-dd hh24:mi:ss')) s\r\n" + 
            "on s.FLOW_INSTANCE_ID = r.FLOW_INSTANCE_ID ORDER BY r.AUDIT_DATE desc"
            ,nativeQuery=true)
    public List<Map<String,Object>> queryDataAuditRecordPage(@Param("st") String startTime,@Param("et") String endTime);
    
    
    /** 查询站点每日的审核情况 已审 未审        **/
    @Query(value = "SELECT TO_CHAR(C_TIME,'yyyy-mm-dd') CTIME, FLOW_INSTANCE_ID FLOWID from T_DATA_AUDIT "
            + "WHERE STATION_MN = :mn and C_TIME BETWEEN TO_DATE(:st," + 
            "   'yyyy-mm-dd')" +
            " and TO_DATE(:et, 'yyyy-mm-dd') " + 
            "GROUP BY TO_CHAR(C_TIME,'yyyy-mm-dd'),FLOW_INSTANCE_ID " + 
            "ORDER BY CTIME asc",nativeQuery=true)
    public List<Map<String,Object>> queryDataAuditGroupByTime(@Param("mn")String mn , @Param("st") String startTime,
            @Param("et") String endTime);
    
    
    /** 查询站点每日的审核情况 已审 未审        **/
    @Query(value = "SELECT TO_CHAR(C_TIME,'yyyy-mm-dd') CTIME,STATION_MN, FLOW_INSTANCE_ID FLOWID from T_DATA_AUDIT "
            + "WHERE STATION_MN in(:mns)  and C_TIME BETWEEN TO_DATE(:st," + 
            "   'yyyy-mm-dd')" +
            " and TO_DATE(:et, 'yyyy-mm-dd') " + 
            "GROUP BY TO_CHAR(C_TIME,'yyyy-mm-dd'),FLOW_INSTANCE_ID,STATION_MN " + 
            "ORDER BY STATION_MN asc",nativeQuery=true)
    public List<Map<String,Object>> queryDataAuditGroupByMnsTime(@Param("mns")List<String> stationMns , @Param("st") String startTime,
            @Param("et") String endTime);
    
    /**
     * 查询流程实例id 根据日期与mn
     * 
     * @author 蔡永顺
     * @return Map<String,Object>
     * @param mn
     * @param date
     * @return
     * @date 2019年5月18日上午11:08:07
     */
    @Query(value = "SELECT FLOW_INSTANCE_ID from T_DATA_AUDIT where STATION_MN = :mn and TO_CHAR(C_TIME,'yyyy-mm-dd') =:date and ROWNUM =1",nativeQuery=true)
    public Map<String,Object> queryFlowIdByMnAndDate(@Param("mn")String mn , @Param("date") String date);
    
    
    /** 查询审核数据有效性 统计数量 **/
    @Query(value="SELECT s.* FROM (\r\n" + 
            "SELECT STATION_MN,PROJ_ID,AUDIT_STATUS,COUNT(AUDIT_STATUS)CNUM from T_DATA_AUDIT where C_TIME BETWEEN TO_DATE(:st, 'yyyy-mm-dd hh24:mi:ss') " + 
            "and TO_DATE(:et, 'yyyy-mm-dd hh24:mi:ss') and STATION_MN in (:mns)" + 
            " GROUP BY STATION_MN,PROJ_ID,AUDIT_STATUS \r\n" + 
            " ORDER BY STATION_MN,PROJ_ID )s\r\n" + 
            "WHERE s.AUDIT_STATUS = '1'",nativeQuery=true)
    public List<Map<String,Object>> statisAuditDataValidate(@Param("mns")List<String> stationMns , @Param("st") String startTime,
            @Param("et") String endTime );
    
    /** 查询审核数据有效性 统计数量计算上传率 排除周期之外的测试数据 **/
    @Query(value = " SELECT * FROM (SELECT STATION_MN,\n"
    		+ "    		                         PROJ_ID,\n" 		                        
    		+ "    		                         REAL_VALUE_STATUS,\n"
    		+ "    		                         COUNT(REAL_VALUE_STATUS) CNUM\n"
    		+ "    		                    from T_DATA_AUDIT\n"
    		+ "    		                   where C_TIME BETWEEN TO_DATE(:startTime, 'yyyy-mm-dd hh24:mi:ss') and\n"
    		+ "    		                         TO_DATE(:endTime, 'yyyy-mm-dd hh24:mi:ss')\n"
    		+ "    		                     and STATION_MN in (:mns)\n"
    		+ "    		                   GROUP BY STATION_MN, PROJ_ID, REAL_VALUE_STATUS\n"
    		+ "    		                   ORDER BY STATION_MN, PROJ_ID) s\n"
    		+ "    		           WHERE s.REAL_VALUE_STATUS = '1'",nativeQuery=true)
    public List<Map<String,Object>> statisAuditDataCountByFlag(@Param("mns")List<String> stationMns , @Param("startTime") String startTime,
            @Param("endTime") String endTime );
    
    
    /** 查询审核数据有效性 统计数量 **/
    @Query(value="SELECT s.* FROM (\r\n" + 
            "SELECT STATION_MN,PROJ_ID,PROJ_NAME,COUNT(1)CNUM from T_DATA_AUDIT where C_TIME BETWEEN TO_DATE(:st, 'yyyy-mm-dd hh24:mi:ss') " + 
            "and TO_DATE(:et, 'yyyy-mm-dd hh24:mi:ss') and STATION_MN in (:mns)" + 
            " GROUP BY STATION_MN,PROJ_ID,PROJ_NAME " + 
            " ORDER BY STATION_MN,PROJ_ID )s " 
            ,nativeQuery=true)
    public List<Map<String,Object>> statisAuditDataCount(@Param("mns")List<String> stationMns , @Param("st") String startTime,
            @Param("et") String endTime );
    
    
    /** 查询站点每日的审核情况 已审 未审        **/
    @Query(value = "SELECT DISTINCT FLOW_INSTANCE_ID, TO_CHAR(C_TIME,'yyyy-mm-dd')CTIME "
            + "from T_DATA_AUDIT where STATION_MN=:mn "
            + "and C_TIME  BETWEEN TO_DATE(:st, 'yyyy-mm-dd')"
            + "and TO_DATE(:et, 'yyyy-mm-dd hh24')",nativeQuery=true)
    public List<Map<String,Object>> queryFlowIdByMnTime(@Param("mn")String stationMn , @Param("st") String startTime,
            @Param("et") String endTime);
    
}



